from rest_framework.response import Response
from rest_framework import status
from rest_framework.viewsets import ModelViewSet
from rest_framework import filters
from django_filters.rest_framework import DjangoFilterBackend
import pymysql, datetime
from django.conf import settings
from django.http import StreamingHttpResponse
from django.utils.encoding import escape_uri_path

from managements.serializers_merchants import *
from equity_mall.utils.pages import MyPage
from managements.filters import *
from equity_mall.utils.utils import get_level_list


class LuckyBagActivityModelViewSet(ModelViewSet):

    pagination_class = MyPage
    queryset = LuckyBagActivity.objects.exclude(activity_status=3).order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return LuckyBagActivityListModelSerializer
        else:
            return LuckyBagActivityRetrieveModelSerializer


class LuckyBagChildActivityDataModelViewSet(ModelViewSet):

    pagination_class = MyPage
    # queryset = LuckyBagChildActivityData.objects.exclude(lucky_status=0).order_by('-id')
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = LuckyBagChildActivityDataFilter
    search_fields = ('business_no', 'merchantuser__mobile')

    def get_serializer_class(self):
        if self.action == 'list':
            return LuckyBagChildActivityDataListModelSerializer
        else:
            return LuckyBagChildActivityDataRetrieveModelSerializer

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = LuckyBagChildActivityData.objects.all()
        elif level.level == 1:
            queryset = LuckyBagChildActivityData.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = LuckyBagChildActivityData.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = LuckyBagChildActivityData.objects.filter(level__district_id=level.id)
        else:
            queryset = LuckyBagChildActivityData.objects.filter(level__branch_id=level.id)
        return queryset.filter(id=0).order_by('-id')

    def export_luckyactivitydata(self, request):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        lucky_status = self.request.query_params.get('lucky_status', None)
        luckybagchildactivity_id = self.request.query_params.get('luckybagchildactivity_id', None)
        merchantuser_id = self.request.query_params.get('merchantuser_id', None)
        lucky_bag_max = self.request.query_params.get('lucky_bag_max', None)
        lucky_bag_min = self.request.query_params.get('lucky_bag_min', None)
        received_time_begin = self.request.query_params.get('received_time_begin', None)
        received_time_end = self.request.query_params.get('received_time_end', None)
        search = self.request.query_params.get('search', None)
        if level.level == 0:
            condition = 'WHERE id!=0 AND id=1'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id}) AND id=1'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id}) AND id=1'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id}) AND id=1'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id}) AND id=1'
        if search:
            condition = condition + f' AND (business_no LIKE "%{search}%")'
        if lucky_status:
            condition = condition + f' AND lucky_status IN ({lucky_status})'
        if received_time_begin:
            condition = condition + f' AND received_time >= "{received_time_begin + " 00:00:00"}"'
        if received_time_end:
            condition = condition + f' AND received_time <= "{received_time_end + " 23:59:59"}"'
        if lucky_bag_max:
            condition = condition + f' AND lucky_bag >= {lucky_bag_max}'
        if lucky_bag_min:
            condition = condition + f' AND lucky_bag <= {lucky_bag_min}'
        if luckybagchildactivity_id:
            condition = condition + f' AND luckybagchildactivity_id IN ({luckybagchildactivity_id})'
        if merchantuser_id:
            condition = condition + f' AND merchantuser_id IN ({merchantuser_id})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT g.*,h.mobile FROM (SELECT e.*,f.activity_type FROM (SELECT c.*,d.city_name,district_name,branch_name,account FROM (SELECT * FROM (SELECT id,business_no,lucky_bag,lucky_status,luckybagchildactivity_id,level_id,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S') AS received_time,merchantuser_id FROM tb_lucky_bag_child_activity_data {condition})AS a LEFT JOIN (SELECT wyfMerchantID,name,short_name FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name,account FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,activity_type FROM tb_lucky_bag_child_activity)AS f ON e.luckybagchildactivity_id=f.id)AS g LEFT JOIN (SELECT id,mobile FROM tb_m_merchant_user)AS h ON g.merchantuser_id=h.id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT g.*,h.mobile FROM (SELECT e.*,f.activity_type FROM (SELECT c.*,d.city_name,district_name,branch_name,account FROM (SELECT * FROM (SELECT id,business_no,lucky_bag,lucky_status,luckybagchildactivity_id,level_id,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S') AS received_time,merchantuser_id FROM tb_lucky_bag_child_activity_data)AS a LEFT JOIN (SELECT wyfMerchantID,name,short_name FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name,account FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,activity_type FROM tb_lucky_bag_child_activity)AS f ON e.luckybagchildactivity_id=f.id)AS g LEFT JOIN (SELECT id,mobile FROM tb_m_merchant_user)AS h ON g.merchantuser_id=h.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', '微邮付商户号', '商户简称', '商户全称', '网点机构号', '所属市公司', '所属区县', '所属网点', '领取状态', '领取福袋数', '领取时间', '活动类型', '领取人手机号']
        cols = ['id', 'wyfMerchantID', 'short_name', 'name', 'account', 'city_name', 'district_name', 'branch_name', 'lucky_status', 'lucky_bag', 'received_time', 'activity_type', 'mobile']
        response = StreamingHttpResponse(self.get_luckyactivitydata_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('88节福袋领取记录.csv'))
        return response

    def get_luckyactivitydata_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        LUCKY_STATUS_ENUM = {
            0: '待抽奖',
            1: '待领取',
            2: '已领取',
            3: '已兑换',
            4: '已过期'
        }
        ACTIVITY_TYPE_ENUM = {
            1: '日日消费礼',
            2: '周周榜单王',
            3: '支付宝大额交易抽奖'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col in ['ryx_smid', 'sxf_smid']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                elif col == 'lucky_status':
                    tmp_str += '"%s",' % (LUCKY_STATUS_ENUM[row[col]])
                elif col == 'activity_type':
                    tmp_str += '"%s",' % (ACTIVITY_TYPE_ENUM[row[col]])
                elif col == 'lucky_bag':
                    if row['lucky_status'] == 2:
                        tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                    else:
                        tmp_str += '"%s",' % ''
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class LuckyBagRecordModelViewSet(ModelViewSet):

    pagination_class = MyPage
    queryset = LuckyBagRecord.objects.all().order_by('-id')
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = LuckyBagRecordFilter
    search_fields = ('merchantuser__mobile', )

    def get_serializer_class(self):
        if self.action == 'list':
            return LuckyBagRecordListModelSerializer
        else:
            return LuckyBagRecordRetrieveModelSerializer


class MerchantNFCDeviceTradeModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantNFCDeviceTradeFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    serializer_class = MerchantNFCDeviceTradeModelSerializer
    search_fields = ('merchant__wyfMerchantID', 'merchant__name', 'sn', 'transaction_id')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        if level.level == 0:
            queryset = MerchantNFCDeviceTrade.objects.filter(pay_type=1)
        elif level.level == 1:
            queryset = MerchantNFCDeviceTrade.objects.filter(level__prov_id=level.id, pay_type=1)
        elif level.level == 2:
            queryset = MerchantNFCDeviceTrade.objects.filter(level__city_id=level.id, pay_type=1)
        elif level.level == 3:
            queryset = MerchantNFCDeviceTrade.objects.filter(level__district_id=level.id, pay_type=1)
        else:
            queryset = MerchantNFCDeviceTrade.objects.filter(level__branch_id=level.id, pay_type=1)
        activity_id = self.request.query_params.get('activity_id', None)
        is_bind = self.request.query_params.get('is_bind', None)
        if activity_id:
            activity = MerchantEightEightActivity.objects.filter(id=activity_id).first()
            if activity:
                trade_time__gte = (activity.activity_start_time + datetime.timedelta(days=-7)).strftime("%Y-%m-%d 00:00:00")
                trade_time__lte = (activity.activity_end_time + datetime.timedelta(days=-7)).strftime("%Y-%m-%d 23:59:59")
                queryset = queryset.filter(trade_time__gte=trade_time__gte, trade_time__lte=trade_time__lte)
        if is_bind == '1':
            queryset = queryset.filter(merchant_id__isnull=False)
        elif is_bind == '2':
            queryset = queryset.filter(merchant_id__isnull=True)
        return queryset.order_by('-trade_time')

    def export_nfc_trade(self, request):
        user = self.request.iser
        level = user.level
        activity_id = self.request.query_params.get('activity_id', None)
        is_bind = self.request.query_params.get('is_bind', None)
        trade_time_begin = self.request.query_params.get('trade_time_begin', None)
        trade_time_end = self.request.query_params.get('trade_time_end', None)
        total_amount_max = self.request.query_params.get('total_amount_max', None)
        total_amount_min = self.request.query_params.get('total_amount_min', None)
        search = self.request.query_params.get('search', None)
        # condition = 'WHERE id!=3750'
        if level.level == 0:
            condition = 'WHERE pay_type=1'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id}) AND pay_type=1'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id}) AND pay_type=1'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id}) AND pay_type=1'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id}) AND pay_type=1'
        if trade_time_begin:
            condition = condition + f' AND trade_time >= "{trade_time_begin + " 00:00:00"}"'
        if trade_time_end:
            condition = condition + f' AND trade_time <= "{trade_time_end + " 23:59:59"}"'
        if total_amount_min:
            condition = condition + f' AND total_amount >= "{total_amount_min}"'
        if total_amount_max:
            condition = condition + f' AND total_amount <= "{total_amount_max}"'
        if activity_id:
            activity = MerchantEightEightActivity.objects.filter(id=activity_id).first()
            if activity:
                trade_time__gte = (activity.activity_start_time + datetime.timedelta(days=-7)).strftime("%Y-%m-%d 00:00:00")
                trade_time__lte = (activity.activity_end_time + datetime.timedelta(days=-7)).strftime("%Y-%m-%d 23:59:59")
                condition = condition + f' AND trade_time >= "{trade_time__gte}"'
                condition = condition + f' AND trade_time <= "{trade_time__lte}"'
        if is_bind == '1':
            condition = condition + f' AND merchant_id IS NOT NULL'
        elif is_bind == '2':
            condition = condition + f' AND merchant_id IS NULL'
        if search:
            condition = condition + f' AND (merchant_id IN (SELECT id FROM tb_merchant WHERE name LIKE "%{search}%" OR wyfMerchantID LIKE "%{search}%") OR sn LIKE "%{search}%" OR transaction_id LIKE "%{search}%")'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.name,wyfMerchantID FROM (SELECT *,DATE_FORMAT(trade_time,'%Y-%m-%d %H:%i:%S') AS trade_time1 FROM tb_merchant_nfc_device_trade {condition})AS a LEFT JOIN (SELECT id,name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.name,wyfMerchantID FROM (SELECT *,DATE_FORMAT(trade_time,'%Y-%m-%d %H:%i:%S') AS trade_time1 FROM tb_merchant_nfc_device_trade)AS a LEFT JOIN (SELECT id,name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', 'SN', '设备类型', '门店名称', '交易号', '交易金额', '交易时间', '微邮付商户号', '商户名称', '归属市公司', '归属区县', '归属网点']
        cols = ['id', 'sn', 'device_type', 'store_name', 'transaction_id', 'total_amount', 'trade_time1', 'wyfMerchantID', 'name', 'city_name', 'district_name', 'branch_name']
        response = StreamingHttpResponse(self.get_nfc_trade_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('结算单信息.csv'))
        return response

    def get_nfc_trade_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col in ['total_amount']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] != None else '-')
                elif col == 'transaction_id':
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"